﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for Tour
/// </summary>
public class Tour
{
    SqlCommand cmd;
    SqlDataReader dr;
	public Tour()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    //lay tour top ^
    public List<ObjTour> getTourByLoaiTourId(string id, string top)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourGetByLoaiTourChaId", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[2];
        sqla[0] = new SqlParameter("loai_tour_id", DbType.String);
        sqla[1] = new SqlParameter("top", DbType.String);
        sqla[0].Value = id;
        sqla[1].Value = top;
        cmd.Parameters.AddRange(sqla);
        dr = cmd.ExecuteReader();
        ObjTour objTour;
        List<ObjTour> lst = new List<ObjTour>();
        while (dr.Read())
        {
            objTour = new ObjTour();
            objTour.Tour_id = int.Parse(dr["tour_id"].ToString());
            objTour.Ten_tour = dr["ten_tour"].ToString();
            objTour.Anh_nho = dr["anh_nho"].ToString();
            objTour.Anh_to = dr["anh_to"].ToString();            
            objTour.Vi_tri = int.Parse(dr["vi_tri"].ToString());
            objTour.Gia_khuyen_mai = int.Parse(dr["gia_khuyen_mai"].ToString());// + Constants.LOAI_TIEN;
            objTour.Gia_thi_truong = int.Parse(dr["gia_thi_truong"].ToString());
            objTour.So_ngay = int.Parse(dr["so_ngay"].ToString());
            objTour.So_dem = int.Parse(dr["so_dem"].ToString());
            objTour.So_cho = int.Parse(dr["so_cho"].ToString());
            objTour.So_cho_da_dat = int.Parse(dr["so_cho_da_dat"].ToString());      
            objTour.Trang_thai = Constants.OFF;
            lst.Add(objTour);
        }
        dr.Close();
        sqlConnection.Close();
        return lst;

    }

    //lay tour noi bat, tour ban chay,...
    public List<ObjTour> getTourByType(string type)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourGetTypeTour", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("@Type", DbType.String);
        sqla[0].Value = type;
        cmd.Parameters.AddRange(sqla);
        dr = cmd.ExecuteReader();
        ObjTour objTour;
        List<ObjTour> lst = new List<ObjTour>();
        while (dr.Read())
        {
            objTour = new ObjTour();
            objTour.Tour_id = int.Parse(dr["tour_id"].ToString());
            objTour.Ten_tour = dr["ten_tour"].ToString();
            objTour.Anh_nho = dr["anh_nho"].ToString();
            objTour.Anh_to = dr["anh_to"].ToString();          
            objTour.Gia_khuyen_mai = int.Parse(dr["gia_khuyen_mai"].ToString());// + Constants.LOAI_TIEN;            
            objTour.Gia_thi_truong = int.Parse(dr["gia_thi_truong"].ToString());// + Constants.LOAI_TIEN;
            objTour.So_ngay= int.Parse(dr["so_ngay"].ToString());
            objTour.So_dem=int.Parse(dr["so_dem"].ToString());
            lst.Add(objTour);
        }
        dr.Close();
        sqlConnection.Close();
        return lst;
    }

    //lay tour moi nhat...

    public List<ObjTour> getTourMoiNhat(string top)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourGetMoiNhat", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[1];
       
        sqla[0] = new SqlParameter("top", DbType.String);
        sqla[0].Value = top;
        cmd.Parameters.AddRange(sqla);
        dr = cmd.ExecuteReader();
        ObjTour objTour;
        List<ObjTour> lst = new List<ObjTour>();
        while (dr.Read())
        {
            objTour = new ObjTour();
            objTour.Tour_id = int.Parse(dr["tour_id"].ToString());
            objTour.Ten_tour = dr["ten_tour"].ToString();
            objTour.Anh_nho = dr["anh_nho"].ToString();
            objTour.Anh_to = dr["anh_to"].ToString();
            objTour.Vi_tri = int.Parse(dr["vi_tri"].ToString());
            objTour.Gia_khuyen_mai = int.Parse(dr["gia_khuyen_mai"].ToString());// + Constants.LOAI_TIEN;
            objTour.Gia_thi_truong = int.Parse(dr["gia_thi_truong"].ToString());
            objTour.So_ngay = int.Parse(dr["so_ngay"].ToString());
            objTour.So_dem = int.Parse(dr["so_dem"].ToString());
            objTour.So_cho = int.Parse(dr["so_cho"].ToString());
            objTour.So_cho_da_dat = int.Parse(dr["so_cho_da_dat"].ToString());
            objTour.Trang_thai = Constants.OFF;
            lst.Add(objTour);
        }
        dr.Close();
        sqlConnection.Close();
        return lst;

    }



    public ObjTour getTourByID(string type)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourGetById", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("@Tour_id", DbType.String);
        sqla[0].Value = type;
        cmd.Parameters.AddRange(sqla);
        dr = cmd.ExecuteReader();
        ObjTour objTour = new ObjTour();
        if (dr.Read())
        {
            objTour.Tour_id = int.Parse(dr["tour_id"].ToString());
            objTour.Ten_tour = dr["ten_tour"].ToString();
            objTour.Anh_to = dr["anh_to"].ToString();
            objTour.Anh_nho = dr["anh_nho"].ToString();
            objTour.Anh_slide = dr["anh_slide"].ToString();
            objTour.Mo_ta_ngan = dr["mo_ta_ngan"].ToString();
            objTour.Noi_dung = dr["noi_dung"].ToString();
            objTour.Vi_tri = int.Parse(dr["vi_tri"].ToString());
            objTour.Loai_tour_id = Int16.Parse(dr["Loai_tour_id"].ToString());
            objTour.Ma_khuyen_mai = Int16.Parse(dr["Ma_khuyen_mai"].ToString());
           
                objTour.Gia_khuyen_mai = int.Parse(dr["gia_khuyen_mai"].ToString());// + Constants.LOAI_TIEN;
          
                objTour.Gia_thi_truong = int.Parse(dr["gia_thi_truong"].ToString());// +Constants.LOAI_TIEN;
                objTour.So_ngay = int.Parse(dr["so_ngay"].ToString());
                objTour.So_dem = int.Parse(dr["so_dem"].ToString());
                objTour.So_cho = int.Parse(dr["so_cho"].ToString());
                objTour.So_cho_da_dat = int.Parse(dr["so_cho_da_dat"].ToString());
                objTour.Ngay_khoi_hanh = dr["ngay_khoi_hanh"].ToString();
                objTour.Ngay_ket_thuc = dr["ngay_ket_thuc"].ToString();


                if (dr["tour_noi_bat"].ToString() == "True")
            {
                objTour.Tour_noi_bat = 1;
            }
                if (dr["tour_ban_chay"].ToString() == "True")
            {
                objTour.Tour_ban_chay = 1;
            }
        }
        dr.Close();
        sqlConnection.Close();
        return objTour;
    }



    public List< ObjTour> getTourBySearch(string type)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourSearch", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("@txt", DbType.String);
        sqla[0].Value = type;
        cmd.Parameters.AddRange(sqla);
        dr = cmd.ExecuteReader();
        ObjTour objTour = new ObjTour();
        List<ObjTour> lst = new List<ObjTour>();
        while (dr.Read())
        {
            objTour = new ObjTour();
            objTour.Tour_id = int.Parse(dr["tour_id"].ToString());
            objTour.Ten_tour = dr["ten_tour"].ToString();
            objTour.Anh_nho = dr["anh_nho"].ToString();         
            objTour.Gia_khuyen_mai = int.Parse(dr["gia_khuyen_mai"].ToString());// + Constants.LOAI_TIEN;            
            objTour.Gia_thi_truong =int.Parse( dr["gia_thi_truong"].ToString());           
            lst.Add(objTour);
        }
        dr.Close();
        sqlConnection.Close();
        return lst;
    }
    public List<ObjTour> getTourBySearch1(string type, string giatu, string giaden)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourSearch1", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[3];
        sqla[0] = new SqlParameter("@txt1", DbType.Int32);
        sqla[1] = new SqlParameter("@giatu", DbType.Int64);
        sqla[2] = new SqlParameter("@giaden", DbType.Int64);
        sqla[0].Value = type;
        sqla[1].Value = giatu;
        sqla[2].Value = giaden;
        cmd.Parameters.AddRange(sqla);
        dr = cmd.ExecuteReader();
        ObjTour objTour = new ObjTour();
        List<ObjTour> lst = new List<ObjTour>();
        while (dr.Read())
        {
            objTour = new ObjTour();
            objTour.Tour_id = int.Parse(dr["tour_id"].ToString());
            objTour.Ten_tour = dr["ten_tour"].ToString();
            objTour.Anh_nho = dr["anh_nho"].ToString();
            objTour.Gia_khuyen_mai = int.Parse(dr["gia_khuyen_mai"].ToString());// + Constants.LOAI_TIEN;            
            objTour.Gia_thi_truong = int.Parse(dr["gia_thi_truong"].ToString());
            lst.Add(objTour);
        }
        dr.Close();
        sqlConnection.Close();
        return lst;
    }
    

         public List< ObjTour> getTourCungLoai(string id)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourGetTourCungLoaiByTourId", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("@Tour_id", DbType.Int32);
        sqla[0].Value = id;
        cmd.Parameters.AddRange(sqla);
        dr = cmd.ExecuteReader();
        ObjTour objTour = new ObjTour();
        List<ObjTour> lst = new List<ObjTour>();
        while (dr.Read())
        {
            objTour = new ObjTour();
            objTour.Tour_id = int.Parse(dr["tour_id"].ToString());
            objTour.Ten_tour = dr["ten_tour"].ToString();
            objTour.Anh_nho = dr["anh_nho"].ToString();          
            objTour.Gia_khuyen_mai = int.Parse(dr["gia_khuyen_mai"].ToString());// + Constants.LOAI_TIEN;   
            objTour.Gia_thi_truong = int.Parse(dr["gia_thi_truong"].ToString());// + Constants.LOAI_TIEN;  
            objTour.So_ngay = int.Parse(dr["so_ngay"].ToString());
            objTour.So_dem = int.Parse(dr["so_dem"].ToString());
            lst.Add(objTour);
        }
        dr.Close();
        sqlConnection.Close();
        return lst;
    }
    public void createTour(ObjTour objTour)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourCreate", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[20];
        sqla[0] = new SqlParameter("@Ten_tour", DbType.String);
        sqla[1] = new SqlParameter("@Ma_tour", DbType.String);
        sqla[2] = new SqlParameter("@Mo_ta_ngan", DbType.String);
        sqla[3] = new SqlParameter("@Gia_thi_truong", DbType.String);
        sqla[4] = new SqlParameter("@Gia_khuyen_mai", DbType.String);
        sqla[5] = new SqlParameter("@Tour_ban_chay", DbType.String);
        sqla[6] = new SqlParameter("@Tour_noi_bat", DbType.String);
        sqla[7] = new SqlParameter("@So_ngay", DbType.String);
        sqla[8] = new SqlParameter("@So_dem", DbType.String);
        sqla[9] = new SqlParameter("@So_cho", DbType.String);
        sqla[10] = new SqlParameter("@So_cho_da_dat", DbType.String);
        sqla[11] = new SqlParameter("@Ngay_khoi_hanh", DbType.String);
        sqla[12] = new SqlParameter("@Ngay_ket_thuc", DbType.String);
        sqla[13] = new SqlParameter("@Anh_to", DbType.String);
        sqla[14] = new SqlParameter("@Anh_nho", DbType.String);
        sqla[15] = new SqlParameter("@Noi_dung", DbType.String);
        sqla[16] = new SqlParameter("Loai_tour_id", DbType.Int16);
        sqla[17] = new SqlParameter("vi_tri", DbType.Int16);
        sqla[18] = new SqlParameter("anh_slide", DbType.String);
        sqla[19] = new SqlParameter("Ma_khuyen_mai", DbType.Int16);
       
        sqla[0].Value = objTour.Ten_tour ;
        sqla[1].Value = objTour.Ma_tour;
        sqla[2].Value = objTour.Mo_ta_ngan;
        sqla[3].Value = objTour.Gia_thi_truong;
        sqla[4].Value = objTour.Gia_khuyen_mai;
        sqla[5].Value = objTour.Tour_ban_chay;
        sqla[6].Value = objTour.Tour_noi_bat;
        sqla[7].Value = objTour.So_ngay;
        sqla[8].Value = objTour.So_dem;
        sqla[9].Value = objTour.So_cho;
        sqla[10].Value = objTour.So_cho_da_dat;
        sqla[11].Value = objTour.Ngay_khoi_hanh;
        sqla[12].Value = objTour.Ngay_ket_thuc;
        sqla[13].Value = objTour.Anh_to;
        sqla[14].Value = objTour.Anh_nho;
        sqla[15].Value = objTour.Noi_dung;
        sqla[16].Value = objTour.Loai_tour_id;
        sqla[17].Value = objTour.Vi_tri;
        sqla[18].Value = objTour.Anh_slide;
        sqla[19].Value = objTour.Ma_khuyen_mai;


        cmd.Parameters.AddRange(sqla);
        cmd.ExecuteNonQuery();
        sqlConnection.Close();
    }



    public void updateTour(ObjTour objTour)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourUpdate", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[21];
        sqla[0] = new SqlParameter("@Ten_tour", DbType.String);
        sqla[1] = new SqlParameter("@Ma_tour", DbType.String);
        sqla[2] = new SqlParameter("@Mo_ta_ngan", DbType.String);
        sqla[3] = new SqlParameter("@Gia_thi_truong", DbType.String);
        sqla[4] = new SqlParameter("@Gia_khuyen_mai", DbType.String);
        sqla[5] = new SqlParameter("@Tour_ban_chay", DbType.String);
        sqla[6] = new SqlParameter("@Tour_noi_bat", DbType.String);
        sqla[7] = new SqlParameter("@So_ngay", DbType.String);
        sqla[8] = new SqlParameter("@So_dem", DbType.String);
        sqla[9] = new SqlParameter("@So_cho", DbType.String);
        sqla[10] = new SqlParameter("@So_cho_da_dat", DbType.String);
        sqla[11] = new SqlParameter("@Ngay_khoi_hanh", DbType.String);
        sqla[12] = new SqlParameter("@Ngay_ket_thuc", DbType.String);
        sqla[13] = new SqlParameter("@Anh_to", DbType.String);
        sqla[14] = new SqlParameter("@Anh_nho", DbType.String);
        sqla[15] = new SqlParameter("@Noi_dung", DbType.String);
        sqla[16] = new SqlParameter("Loai_tour_id", DbType.Int16);
        sqla[17] = new SqlParameter("vi_tri", DbType.Int16);
        sqla[18] = new SqlParameter("Tour_id", DbType.Int16);
        sqla[19] = new SqlParameter("anh_slide", DbType.String);
        sqla[20] = new SqlParameter("Ma_khuyen_mai", DbType.Int16);

        sqla[0].Value = objTour.Ten_tour;
        sqla[1].Value = objTour.Ma_tour;
        sqla[2].Value = objTour.Mo_ta_ngan;
        sqla[3].Value = objTour.Gia_thi_truong;
        sqla[4].Value = objTour.Gia_khuyen_mai;
        sqla[5].Value = objTour.Tour_ban_chay;
        sqla[6].Value = objTour.Tour_noi_bat;
        sqla[7].Value = objTour.So_ngay;
        sqla[8].Value = objTour.So_dem;
        sqla[9].Value = objTour.So_cho;
        sqla[10].Value = objTour.So_cho_da_dat;
        sqla[11].Value = objTour.Ngay_khoi_hanh;
        sqla[12].Value = objTour.Ngay_ket_thuc;
        sqla[13].Value = objTour.Anh_to;
        sqla[14].Value = objTour.Anh_nho;
        sqla[15].Value = objTour.Noi_dung;
        sqla[16].Value = objTour.Loai_tour_id;
        sqla[17].Value = objTour.Vi_tri;
        sqla[18].Value = objTour.Tour_id;
        sqla[19].Value = objTour.Anh_slide;
        sqla[20].Value = objTour.Ma_khuyen_mai;


        cmd.Parameters.AddRange(sqla);
        cmd.ExecuteNonQuery();
        sqlConnection.Close();
    }

    public void deleteTour(string id)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourDeleteById", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("tour_id", DbType.Int32);
        sqla[0].Value = id;
        cmd.Parameters.AddRange(sqla);
        cmd.ExecuteNonQuery();
        sqlConnection.Close();
    }

    public void deleteTourByXml(string xml)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourDeleteByXml", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("doc", DbType.Int32);
        sqla[0].Value = xml;
        cmd.Parameters.AddRange(sqla);
        cmd.ExecuteNonQuery();
        sqlConnection.Close();
    }

    public void updateViTriLoaiTourByXml(string xml)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourUpdateViTriByXml", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("Doc", DbType.String);
        sqla[0].Value = xml;
        cmd.Parameters.AddRange(sqla);
        cmd.ExecuteNonQuery();
        sqlConnection.Close();
        sqlConnection.Dispose();

    }

    public List<ObjTour> getTourSlide()
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourGetSlide", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        dr = cmd.ExecuteReader();
        ObjTour objTour = new ObjTour();
        List<ObjTour> lst = new List<ObjTour>();
        while (dr.Read())
        {
            objTour = new ObjTour();
            objTour.Tour_id = int.Parse(dr["tour_id"].ToString());
            objTour.Ten_tour = dr["ten_tour"].ToString();
            objTour.Anh_slide = dr["anh_slide"].ToString();

            lst.Add(objTour);
        }
        dr.Close();
        sqlConnection.Close();
        return lst;
    }
}
